package ca.athabascau.quizmaster.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;

public class DataAccess {

	private Comp667ProjectSettings settings;

//	public static Object[] getAnswers()
//	{
//		DataAccess da=new DataAccess();
//		try {
//			return da.getAnswersArray().toArray();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return null;
//	}
	// private DataAccess

	public DataAccess() {
		settings = new Comp667ProjectSettings();

	}

	private Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(settings.getDatabaseLocation());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public CompleteQuestion getRandomCompleteQuestion(String difficulty)
	{
		CompleteQuestion cQ=new CompleteQuestion();
		
		try {
			System.out.println(difficulty);
			int count=getQuestionCount(difficulty, "Geography", "Question");
			//This only works with contiguous ids
			Random rndGen=new Random();
			int target=rndGen.nextInt(count);
			target+=1;
			
			ArrayList<Answer> answers= getAnswersArray(target);
			Answer a=null;
			for(int x=0;x<4;x++)
			{
				a=answers.get(x);
				if(a.getAnswerNo()==a.getQuestion().getCorrectAnswerNumber())
					cQ.setCorrectAnswer(a);
				switch(x)
				{
				case 0:
				//difficulty and field should be the same for all.
					cQ.setDifficulty(difficulty);
					cQ.setField(a.getQuestion().getField().getName());
					cQ.setAnswer1(a);
					break;
				case 1:
					cQ.setAnswer2(a);
					break;
				case 2:
					cQ.setAnswer3(a);
					break;
				case 3:
					cQ.setAnswer4(a);
					break;
				}
			}
		
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		
		return cQ;
	}
	
	@SuppressWarnings("finally")
	private ArrayList<Answer> getAnswersArray() throws SQLException {
		ArrayList<Answer> answers = new ArrayList<Answer>();
		Connection conn = null;
		PreparedStatement getAnswersQuery = null;
		ResultSet results;

		try {
			conn = getConnection();
			// acknowledged that this is clearly a sql injection risk but in the
			// context of the assignment it is a quick and fast solution
			getAnswersQuery = conn.prepareStatement(settings
					.getLoadAllAnwersQuery());
			results = getAnswersQuery.executeQuery();
			while (results.next()) {
				answers.add(new Answer(
						new Question(
								results.getInt("qid"),
								results.getString("text"),
								results.getInt("correctanswernumber"),
								new BaseCodeTable(QuizMasterCodeTables.FIELD,
										results.getInt("FIELD_id"), results
												.getString("FIELD_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.DIFFICULTY_LEVEL,
										results.getInt("DIFFICULTY_LEVEL_id"),
										results.getString("DIFFICULTY_LEVEL_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.LEARNING_OBJECT_TYPE,
										results.getInt("LEARNING_OBJECT_TYPE_id"),
										results.getString("LEARNING_OBJECT_TYPE_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.LEARNING_OBJECT_TYPE,
										results.getInt("LEARNING_OBJECT_TYPE_id"),
										results.getString("LEARNING_OBJECT_TYPE_name"))),
						results.getInt("answerno"), results
								.getString("answertext"), results
								.getString("answerid")));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (getAnswersQuery != null) {
				getAnswersQuery.close();
			}

			return answers;

		}
	}

	@SuppressWarnings("finally")
	private ArrayList<Answer> getAnswersArray(int questionid) throws SQLException {
		ArrayList<Answer> answers = new ArrayList<Answer>();
		Connection conn = null;
		PreparedStatement getAnswersQuery = null;
		ResultSet results;

		try {
			conn = getConnection();
			// acknowledged that this is clearly a sql injection risk but in the
			// context of the assignment it is a quick and fast solution
			getAnswersQuery = conn.prepareStatement(settings
					.getLoadAllAnswersQueryFiltered());
			getAnswersQuery.setInt(1,questionid);
			results = getAnswersQuery.executeQuery();
			while (results.next()) {
				answers.add(new Answer(
						new Question(
								results.getInt("qid"),
								results.getString("text"),
								results.getInt("correctanswernumber"),
								new BaseCodeTable(QuizMasterCodeTables.FIELD,
										results.getInt("FIELD_id"), results
												.getString("FIELD_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.DIFFICULTY_LEVEL,
										results.getInt("DIFFICULTY_LEVEL_id"),
										results.getString("DIFFICULTY_LEVEL_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.LEARNING_OBJECT_TYPE,
										results.getInt("LEARNING_OBJECT_TYPE_id"),
										results.getString("LEARNING_OBJECT_TYPE_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.LEARNING_OBJECT_TYPE,
										results.getInt("LEARNING_OBJECT_TYPE_id"),
										results.getString("LEARNING_OBJECT_TYPE_name"))),
						results.getInt("answerno"), results
								.getString("answertext"), results
								.getString("answerid")));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (getAnswersQuery != null) {
				getAnswersQuery.close();
			}

			return answers;

		}
	}
	
	@SuppressWarnings("finally")
	public String getOntology() throws SQLException {
		String ontology =null;
		Connection conn = null;
		PreparedStatement getOntologyQuery = null;
		ResultSet results;

		try {
			conn = getConnection();
				getOntologyQuery = conn.prepareStatement(settings
					.getSingleOntologyQuery());
			results = getOntologyQuery.executeQuery();
			while (results.next()) {
				ontology=results.getString("ontology_text");

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (getOntologyQuery != null) {
				getOntologyQuery.close();
			}

			return ontology;

		}
	}


	@SuppressWarnings("finally")
	private int getQuestionCount(String difficulty,String field,String learningObjType) throws SQLException {
		int count =0;
		Connection conn = null;
		PreparedStatement getQuestionCountQuery = null;
		ResultSet results;

		try {
			conn = getConnection();
				getQuestionCountQuery = conn.prepareStatement(settings
					.getQuestionCountQuery());
				getQuestionCountQuery.setString(1, difficulty);
				getQuestionCountQuery.setString(2, field);
				getQuestionCountQuery.setString(3,learningObjType);
				
			results = getQuestionCountQuery.executeQuery();
			while (results.next()) {
				count=results.getInt(1);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (getQuestionCountQuery != null) {
				getQuestionCountQuery.close();
			}

			return count;

		}
	}
	
	public static void main(String[] args) {
		DataAccess da = new DataAccess();
String diff="Easy";
		ArrayList<Answer> answers = null;
		try {
			int count=da.getQuestionCount(diff, "Geography", "Question");
			answers = da.getAnswersArray(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for (Answer answer : answers)
			System.out.println(answer.getAnswerId());
	}
}
